const moment = require('moment');
const db = require('../dbbase/db');
const getData = require("../util/data");
const uid = require("node-uuid");

//获取绩效信息
exports.getPerfromInfo = (req, res, next) => {
    const startDate = getData.getCurrentMonthFirst();
    const endDate = getData.getCurrentMonthLast();
    const sql = " SELECT shijian time,SUM(gshj) gshj FROM "
    + "(select to_char(t.BTIME, 'YYYY-MM-DD') shijian, "
    + "ROUND(((COUNT(*)-COUNT(PDQUID))/COUNT(*)/WORKINDICATOR1)*(COUNT(*)*MATE_INDICATOR), 1) gshj " 
    + "from mwdk_pa_work_library t " 
    + "WHERE t.btime>=TO_DATE('" + startDate + "','yyyy-mm-dd HH24:mi:ss') and t.btime<TO_DATE('" + endDate + "','yyyy-mm-dd HH24:mi:ss') "
    + "and MATE_TYPE not like '%去毛刺%' "
    + "GROUP BY to_char(t.BTIME, 'YYYY-MM-DD'),WORKNAME1,MATE_INDICATOR,WORKINDICATOR1 "
    + "ORDER by to_char(t.BTIME, 'YYYY-MM-DD') "
    + ") "
    + "GROUP BY shijian "
    + "ORDER BY shijian desc "
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取gongshi排名信息
exports.getPerfromPaim = (req, res, next) => {
    const startDate = getData.getCurrentDayStart();
    const endDate = getData.getCurrentDayEnd();
    console.log(startDate);
    console.log(endDate);

    const sql = "SELECT name name,SUM(gshj) gs FROM "
    + "(select WORKNAME1 name,"
    + "ROUND(((COUNT(*)-COUNT(PDQUID))/COUNT(*)/WORKINDICATOR1)*(COUNT(*)*MATE_INDICATOR), 1) gshj "
    + "from mwdk_pa_work_library t "
    + "WHERE t.btime>=TO_DATE('" + startDate + "','yyyy-mm-dd hh24:mi:ss') and t.btime<TO_DATE('" + endDate + "','yyyy-mm-dd hh24:mi:ss') "
   
    + "GROUP BY WORKNAME1,WORKID1POIT,MATE_INDICATOR,WORKINDICATOR1 "
    + "ORDER by WORKNAME1) "
    + "GROUP BY name "
    + "ORDER BY gs DESC ";
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取个人工时信息
exports.getPersonalTime = (req, res, next) => {
    const userCode = JSON.parse(req.body.user).usercode
    const startDate = getData.getCurrentMonthFirst();
    const endDate = getData.getCurrentMonthLast();
    const sql = "SELECT usercode usercode,SUM(totle) totle,SUM(defect) defect,SUM(useful) useful,SUM(gshj) gs FROM "
    + "(select WORKID1 usercode,COUNT(*) totle,COUNT(PDQUID) defect,COUNT(*)-COUNT(PDQUID) useful, "
    + "ROUND(((COUNT(*)-COUNT(PDQUID))/COUNT(*)/WORKINDICATOR1)*(COUNT(*)*MATE_INDICATOR), 1) gshj "
    + "from mwdk_pa_work_library t "
    + "WHERE t.btime>=TO_DATE('" + startDate + "','yyyy-mm-dd hh24:mi:ss') and t.btime<TO_DATE('" + endDate + "','yyyy-mm-dd hh24:mi:ss') "
    + "and WORKID1POIT not like '精车去毛刺%' "
    + "GROUP BY WORKID1,MATE_INDICATOR,WORKINDICATOR1) "
    + "WHERE usercode='"+ userCode +"' "
    + "GROUP BY usercode ";
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取辅助工时list
exports.getAssistTimeList = (req, res, next) => {
    const sql = "select * from MOV_APP_APPLYLIST"
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取个人辅助工时
exports.getPersonalFuzhuTime = (req, res, next) => {
    const usercode = JSON.parse(req.body.user).usercode
    const sql = "select STEPPER*STANDARDTIME totleTime from MOV_APP_USERAPPLY WHERE USERCODE='"+ usercode +"' and STATUS='1'"
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//提交辅助工时申请
exports.submitApplyInfo = (req, res, next) => {
    req.body.id = uid.v1();
    req.body.status = 0;
    const applyData = req.body;
    const sql = "INSERT INTO MOV_APP_USERAPPLY(id, username, usercode, applycontent, stepper, starttime, endtime, status, standardTime) "
    + "VALUES (:1, :2, :3, :4, :5, to_date(:6,'yyyy-MM-dd HH24:mi'), to_date(:7,'yyyy-MM-dd HH24:mi'), :8, :9) ";
    const params = [applyData.id, applyData.username, 
        applyData.usercode, applyData.applyContent, 
        applyData.stepper,applyData.startTime, 
        applyData.endTime,applyData.status,
        applyData.standardTime];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取已申请辅助工时list
exports.getDoneApplyTimeList = (req, res, next) => {
    const user = req.body.user;
    const sql = "select *  from MOV_APP_USERAPPLY WHERE USERCODE='"+ user.usercode +"'"
    const params = [];
    db.query(sql, params).then((result) => {
        for (let i = 0; i < result.length; i++) {
            result[i].ENDTIME = moment(result[i].ENDTIME).format('YYYY-MM-DD HH:mm:ss');
        }
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取辅助工时审核list
exports.getApplyCheckList = (req, res, next) => {
    const sql = "select *  from MOV_APP_USERAPPLY "
    const params = [];
    db.query(sql, params).then((result) => {
        for (let i = 0; i < result.length; i++) {
            result[i].ENDTIME = moment(result[i].ENDTIME).format('YYYY-MM-DD HH:mm:ss');
        }
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//辅助工时申请通过
exports.updateApplyTimeList = (req, res, next) => {
    const sql = "UPDATE MOV_APP_USERAPPLY SET status = '" + req.body.status +"' WHERE id = '"+ req.body.id +"' "
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//辅助工时申请拒绝
exports.refuseApplyTimeList = (req, res, next) => {
    const sql = "UPDATE MOV_APP_USERAPPLY SET status = '" + req.body.status +"' ,content = '"+ req.body.content +"' WHERE id = '"+ req.body.id +"' "
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}